import openpyxl
def read_all_data(filename,sheetname):
    book = openpyxl.load_workbook(filename=filename)
    sh1 = book[sheetname]
    all_case = []
    for i in range(2, sh1.max_row + 1): # 循环发送登录接口：必须要传入3要素，url,header,data
        case = dict(
            caseid=sh1.cell(i, 1).value, # 用例编号
            head=eval(sh1.cell(i, 5).value), # 请求头
            url=sh1.cell(i, 6).value, # 请求地址
            data=eval(sh1.cell(i, 7).value), # 请求体
            exp_res=eval(sh1.cell(i, 8).value)) # 预期结果
        all_case.append(case)
    return all_case

def write_res(filename,sheetname,row,test_res):
    book = openpyxl.load_workbook(filename=filename)
    sh1 = book[sheetname]
    sh1.cell(row, 9).value = test_res
    book.save(filename=filename)